;;;;;;;;;;;
(require '[clojure.java.jdbc :as jdbc])

(def db-spec {:classname "org.sqlite.JDBC"
              :subprotocol "sqlite"
              :subname "test.db"})

(defn setup
  []
  (jdbc/with-connection db-spec
    (jdbc/create-table :country
      [:id "integer primary key"]
      [:country "varchar"])
    (jdbc/create-table :author
      [:id "integer primary key"]
      [:country_id "integer constraint fk_country_id
references country (id)"]
      [:first_name "varchar"]
      [:last_name "varchar"])
    (jdbc/insert-records :country
      {:id 1 :country "USA"}
      {:id 2 :country "Canada"}
      {:id 3 :country "France"})
    (jdbc/insert-records :author
      {:first_name "Chas" :last_name "Emerick" :country_id 1}
      {:first_name "Christophe" :last_name "Grand" :country_id 3}
      {:first_name "Brian" :last_name "Carper" :country_id 2}
      {:first_name "Mark" :last_name "Twain" :country_id 1})))

(setup)
;= ({:id 1, :country_id 1, :first_name "Chas", :last_name "Emerick"}
;= {:id 2, :country_id 3, :first_name "Christophe", :last_name "Grand"}
;= {:id 3, :country_id 2, :first_name "Brian", :last_name "Carper"}
;= {:id 4, :country_id 1, :first_name "Mark", :last_name "Twain"})

(use '[korma db core])

(defdb korma-db db-spec)

(declare author)

(defentity country
  (pk :id)
  (has-many author))

(defentity author
  (pk :id)
  (table :author)
  (belongs-to country))

;;;;;;;
(select author
  (with country)
  (where {:first_name "Chas"}))
;= [{:id 1, :country_id 1, :first_name "Chas",:last_name "Emerick", :id_2 1, :country "USA"}]

(select author
  (with country)
  (where (like :first_name "Ch%"))
  (order :last_name :asc)
  (limit 1)
  (offset 1))
;= [{:id 2, :country_id 3, :first_name "Christophe",:last_name "Grand", :id_2 3, :country "France"}]

(select author
  (fields :first_name :last_name)
  (where (or (like :last_name "C%")
           (= :first_name "Mark"))))
;= [{:first_name "Brian", :last_name "Carper"}
;= {:first_name "Mark", :last_name "Twain"}]

(println (sql-only (select author
                     (with country)
                     (where (like :first_name "Ch%"))
                     (order :last_name :asc)
                     (limit 1)
                     (offset 1))))
;= ; SELECT "author".* FROM "author" LEFT JOIN "country"
;= ; ON "country"."id" = "author"."country_id"
;= ; WHERE "author"."first_name" LIKE ?
;= ; ORDER BY "author"."last_name" ASC LIMIT 1 OFFSET 1

(def query (-> (select* author)
             (fields :last_name :first_name)
             (limit 5)))
;= #'user/query

{:group [],
 :from
 [{:table "author",
   :name "author",
   :pk :id,
   :db nil,
   :transforms (),
   :prepares (),
   :fields [],
   :rel
   {"country"
    #<Delay@54f690e4:
    {:table "country",
     :alias nil,
     :rel-type :belongs-to,
     :pk {:korma.sql.utils/generated "\"country\".\"id\""},
     :fk
     {:korma.sql.utils/generated "\"author\".\"country_id\""}}>}}],
 :joins [],
 :where [],
 :ent
 {:table "author",
  :name "author",
  :pk :id,
  :db nil,
  :transforms (),
  :prepares (),
  :fields [],
  :rel
  {"country"
   #<Delay@54f690e4:
   {:table "country",
    :alias nil,
    :rel-type :belongs-to,
    :pk {:korma.sql.utils/generated "\"country\".\"id\""},
    :fk {:korma.sql.utils/generated "\"author\".\"country_id\""}}>}},
 :limit 5,
 :type :select,
 :alias nil,
 :options nil,
 :fields (:last_name :first_name),
 :results :results,
 :table "author",
 :order [],
 :modifiers [],
 :db nil,
 :aliases #{}}

;;;;;;
(def employees (where (select* employees) {:type "employee"}))
;; ... later ...

(let [managers (-> employees
                 (where {:role "manager"})
                 (order :last_name))]
  (doseq [e (exec managers)]
    ; ... process results ...
    ))

(def humans (-> (select* humans)
              (order :date_of_birth)))

(let [kings-of-germany (-> humans
                         (where {:country "Germany" :profession "King"}))]
  (doseq [start (range 0 100 10)
          k (select kings-of-germany
              (offset start)
              (limit 10))]
    ;...
    ))

